-- 充值记录
CREATE TABLE recharges (
    id SERIAL NOT NULL,
    bill_no CHAR(20) NOT NULL DEFAULT '',               -- 订单号
    date DATE NOT NULL DEFAULT CURRENT_DATE,            -- 日期
    user_id INT NOT NULL,                               -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',          -- 用户名称
    amount DECIMAL(16, 4) NOT NULL DEFAULT 0,           -- 支付金额
    is_first SMALLINT NOT NULL DEFAULT 0,               -- 是否首存
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 添加时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 修改时间
    status SMALLINT NOT NULL DEFAULT 0,                 -- 状态, 0: 待审核; 1: 成功; 2: 失败; 3: 冻结
    audit_admin_id INT NOT NULL DEFAULT 0,              -- 审核管理员编号
    audit_admin_name VARCHAR(20) NOT NULL DEFAULT '',   -- 审核管理员名称
    audit_remark VARCHAR(200) NOT NULL DEFAULT '',      -- 审核备注
    audited TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 审核时间
    pay_type SMALLINT NOT NULL DEFAULT 0,               -- 支付类型, 0: 网银转账; 1: 微信; 2: 支付宝
    pay_bank_id INT NOT NULL DEFAULT 0,                 -- 支付银行编号
    pay_receipt VARCHAR(50) NOT NULL DEFAULT 0,         -- 支付回执单号
    reason VARCHAR(200) NOT NULL DEFAULT '',            -- 拒绝原因
    finished TIMESTAMPTZ,                               -- 完成时间/成功时间
    FOREIGN KEY (user_id) REFERENCES users(id),
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);

INSERT INTO recharges (bill_no, user_id, user_name, amount) VALUES ('R2020051210101089313', 45, 'temp_name1', '345');

-- 索引
CREATE INDEX ON recharges(date);        -- 日期
CREATE INDEX ON recharges(created);     -- 创建时间

-- 分区/按月
CREATE TABLE recharges_202005 PARTITION OF recharges FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE recharges_202006 PARTITION OF recharges FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE recharges_202007 PARTITION OF recharges FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE recharges_202008 PARTITION OF recharges FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE recharges_202009 PARTITION OF recharges FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE recharges_202010 PARTITION OF recharges FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE recharges_202011 PARTITION OF recharges FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE recharges_202012 PARTITION OF recharges FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE recharges_202101 PARTITION OF recharges FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE recharges_202102 PARTITION OF recharges FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE recharges_202103 PARTITION OF recharges FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE recharges_202104 PARTITION OF recharges FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE recharges_202105 PARTITION OF recharges FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE recharges_202106 PARTITION OF recharges FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE recharges_202107 PARTITION OF recharges FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE recharges_202108 PARTITION OF recharges FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE recharges_202109 PARTITION OF recharges FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE recharges_202110 PARTITION OF recharges FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE recharges_202111 PARTITION OF recharges FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE recharges_202112 PARTITION OF recharges FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');

--  触发器
--  修改时保存账变信息
CREATE OR REPLACE FUNCTION recharge_update_trigger()
    RETURNS TRIGGER
AS $$
    BEGIN
        IF NEW.status = 0 THEN -- 不能修改为0
            RAISE EXCEPTION '修改充值状态的值不能为 0';
        ELSEIF NEW.status = 1 THEN -- 表示充值成功
            EXECUTE account_change_save(NEW.bill_no, NEW.user_id, 1, 1, NEW.amount);
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER recharge_update
    AFTER UPDATE ON recharges
    FOR EACH ROW EXECUTE PROCEDURE recharge_update_trigger();

-- 保存信息时, 状态值必须为0
CREATE OR REPLACE FUNCTION recharge_create_trigger()
    RETURN TRIGGER
AS $$
    BEGIN
        -- 插入数据时此值必须为0
        -- 如果状态值为2将导致账变出现误差
        IF NEW.status <> 0 THEN
            RAISE EXCEPTION '充值状态的值不能非0';
        END IF;
        SELECT id FROM recharges WHERE user_id = NEW.user_id LIMIT 1;
        IF NOT FOUND THEN -- 是否是首次存款
            NEW.is_first = 1;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER recharge_create
    BEFORE INSERT ON recharges
    FOR EACH ROW EXECUTE PROCEDURE recharge_create_trigger();
